package uk.ac.aber.paws.server.db;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Hashtable;
import java.util.Vector;
import java.util.logging.Level;

import com.microsoft.sqlserver.jdbc.SQLServerException;
import com.mysql.jdbc.ResultSetMetaData;

import uk.ac.aber.paws.client.db.DBCall;
import uk.ac.aber.paws.server.core.PAWSServer;

/**
 * @author Rob Johnson, Aberystwyth University
 * @email rob.johnson@aber.ac.uk
 * @date 16th August 2011
 * 
 * DBCallProcessor.java
 * 
 * Contains all generic constants used within the application
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

public class DBCallProcessor {
	
	/**
	 * This procedure builds up the stored procedure call by parsing all parameters passed in. It also
	 * constructs a CallableStatement object and set the relevant datatypes for each required parameter.
	 * @param conn The open connection to the database in which to make the call.
	 * @param callObject The DBCall object that stores all the parameters to be passed into the CallableStatement.
	 * @return Returns false if database error occurred, true if successful.
	 */
	public static boolean executeCall(Connection conn, DBCall callObject) {
		
		String call = "{call "+callObject.getStoredProc()+"(";
		
		try {
			
			for (int i=0;i<callObject.getParamList().size();i++) {
				call += "?,";
			}
			
			// trim off the last unwanted comma
			if (callObject.getParamList().size() > 0) {
				call = call.substring(0, call.length()-1);
			}
			
			call += ")}";
			
			CallableStatement cs = conn.prepareCall(call);
			
			for (int i=0;i<callObject.getParamList().size();i++) {
				
				Object thisItem = callObject.getParamList().get(i);
				
				
				if (thisItem != null) {
					if (thisItem instanceof String) {
						cs.setString((i+1),(String)thisItem);
					}
					else if (thisItem instanceof Integer) {
						cs.setInt((i+1),((Integer)thisItem).intValue());
					}
					else if (thisItem instanceof Double) {
						cs.setDouble((i+1),((Double)thisItem).doubleValue());
					}
					else if (thisItem instanceof Float) {
						cs.setDouble((i+1),((Float)thisItem).floatValue());
					}
					else if (thisItem instanceof Long) {
						cs.setLong((i+1),((Long)thisItem).longValue());
					}
					else if (thisItem instanceof Timestamp) {
						cs.setTimestamp((i+1),(Timestamp)thisItem);
					}
					else if (thisItem instanceof Hashtable) {
						
						ByteArrayOutputStream bytes = new ByteArrayOutputStream();
						ObjectOutputStream oos = new ObjectOutputStream(bytes);
						oos.writeObject(thisItem);
						
						byte[] byteArray = (byte[])(bytes.toByteArray());

						ByteArrayInputStream stream = new ByteArrayInputStream(byteArray);

						oos.flush();
						oos.close();
						
						cs.setBinaryStream((i+1),stream,byteArray.length);
					}
				}
				else {
					cs.setString((i+1),null);
				}
			}
			
			ResultSet queryResult = null;
			int updateResult = 0;
			
			if (callObject.getQueryType() == DBCall.QUERY) {
				
				queryResult = cs.executeQuery();
					
				// need to convert ResultSet to Vector as it is not serialisable
				Vector<Hashtable<String,Object>> sendableData = convertResultSetToVector(queryResult);

				(callObject.getResponse()).put("QUERYRESULT", sendableData);
			}
			else if (callObject.getQueryType() == DBCall.UPDATE) {
				
				updateResult = cs.executeUpdate();
				
				(callObject.getResponse()).put("UPDATERESULT", updateResult);
			}
			
		}
		catch (SQLServerException e) {
			System.out.println("SQLServerException: problem with "+call);
			
			// stored procedure can't be found, so reject it - we don't want it put back into the queue!
			return true;
		}
		catch (Exception e) {
			return false;
		}
	
		return true;
	}
	
	/**
	 * Parses the return information from the database and structures the results into a Vector that is then passed back
	 * to the caller. This is typically used in a query statement. 
	 * @param rs The ResultSet containing the information passed back from the query
	 * @return Returns the a vector containing the structures results
	 * @throws SQLException
	 */
	private static Vector<Hashtable<String,Object>> convertResultSetToVector(ResultSet rs) throws SQLException {
		
		Vector<Hashtable<String,Object>> data = new Vector<Hashtable<String,Object>>();
		
		java.sql.ResultSetMetaData rsmd = rs.getMetaData();
	    int numColumns = rsmd.getColumnCount();
	    
	    while (rs.next()) {
	    	
	    	Hashtable<String,Object> thisObject = new Hashtable<String,Object>();
	    	
	    	for (int i=1;i<=numColumns;i++) {
	    		
	    		if (rsmd.getColumnType(i) == Types.VARCHAR) {
	    			if (rs.getString(rsmd.getColumnLabel(i)) != null) {
	    				thisObject.put(rsmd.getColumnLabel(i),rs.getString(rsmd.getColumnLabel(i)));
	    			}
	    		}
	    		else if (rsmd.getColumnType(i) == Types.DECIMAL) {
	    			thisObject.put(rsmd.getColumnLabel(i),rs.getDouble(rsmd.getColumnLabel(i)));
	    		}
	    		else if (rsmd.getColumnType(i) == Types.FLOAT) {
	    			thisObject.put(rsmd.getColumnLabel(i),rs.getFloat(rsmd.getColumnLabel(i)));
	    		}
	    		else if (rsmd.getColumnType(i) == Types.BIGINT) {
	    			thisObject.put(rsmd.getColumnLabel(i),rs.getLong(rsmd.getColumnLabel(i)));
	    		}
	    		else if (rsmd.getColumnType(i) == Types.SMALLINT) {
	    			thisObject.put(rsmd.getColumnLabel(i),rs.getInt(rsmd.getColumnLabel(i)));
	    		}
	    		else if (rsmd.getColumnType(i) == Types.TIMESTAMP) {
	    			if (rs.getTimestamp(rsmd.getColumnLabel(i)) != null) {
	    				thisObject.put(rsmd.getColumnLabel(i),rs.getTimestamp(rsmd.getColumnLabel(i)));
	    			}
	    		}
	    		else if (rsmd.getColumnType(i) == Types.VARBINARY) {
	    			
	    			if (rs.getTimestamp(rsmd.getColumnLabel(i)) != null) {
	    				try {
		    				Object stream = rs.getBinaryStream(rsmd.getColumnLabel(i));			
							ObjectInputStream ois = new ObjectInputStream((InputStream)stream);			
							Object result = ois.readObject();
						
							Hashtable<String,Object> dataHash = (Hashtable<String,Object>)result;
							thisObject.put(rsmd.getColumnLabel(i),dataHash);
	    				}
	    				catch (IOException ex) {
	    					ex.printStackTrace();
	    				}
	    				catch (ClassNotFoundException ex) {
	    					ex.printStackTrace();
	    				}
	    			}
	    		}
	    			
	    				
	    	}
	    	
	    	data.add(thisObject);
	    }
	    
		return data;
	}
}
